IF COL_LENGTH('Branch', 'AddressId') IS NULL
	ALTER TABLE Branch add AddressId INT NULL

GO



IF NOT EXISTS (SELECT * FROM dbo.Setting WHERE Name='logisticsInformationSettings.LogisticsServiceStoreId')
INSERT INTO dbo.Setting
        ( Name, Value )
VALUES  ( N'logisticsInformationSettings.LogisticsServiceStoreId', -- Name - nvarchar(200)
          N'23'  -- Value - nvarchar(2000)
          )

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.catalog.productvariants.openbox')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 , -- LanguageId - int
          N'admin.catalog.productvariants.openbox' , -- ResourceName - nvarchar(200)
          N'Open box'  -- ResourceValue - nvarchar(max)
        )


UPDATE [Order]

SET StoreId = 23

WHERE OrderKindId = 10 and Exists
(
	SELECT * FROM OrderProductVariant 
	inner join ProductVariant ON OrderProductVariant.ProductVariantId = ProductVariant.Id
	inner join Product ON ProductVariant.ProductId = Product.Id
	WHERE OrderProductVariant.OrderId = [Order].Id and Product.StoreId = 23
)


INSERT INTO [PermissionRecord_Role_Mapping]
(
	CustomerRole_Id,PermissionRecord_Id
)
VALUES
(
	(
		SELECT Id  from CustomerRole WHERE NAME = 'Warehouse Staff'
	),
	(
		SELECT ID FROM PermissionRecord WHERE SystemName = 'AccessAdminPanel'
	)
)

IF NOT EXISTS
(
	SELECT * FROM  PermissionRecord_Role_Mapping
    WHERE CustomerRole_Id IN
	(
		SELECT ID FROM CustomerRole where Name = 'Warehouse Staff'
	) 
	AND PermissionRecord_Id IN
	(
		SELECT ID FROM PermissionRecord where SystemName = 'ManageOrders'
	)
)
INSERT INTO PermissionRecord_Role_Mapping
(
	CustomerRole_Id,PermissionRecord_Id
)
VALUES
(
	(SELECT ID FROM CustomerRole where Name = 'Warehouse Staff'),
	(SELECT ID FROM PermissionRecord where SystemName = 'ManageOrders')
)

 DECLARE @StoreId int,@Name nvarchar(max),
		@Email nvarchar(max),
		@CountryId int,@StateProvinceId int,
		@City nvarchar(max),@Address1 nvarchar(max),
		@ZipPostalCode nvarchar(max),@PhoneNumber nvarchar(max)
if EXISTS(select * from Store where Name='SpotsHUB')
begin
select @StoreId=Id from Store where Name='SpotsHUB'
set @Email='info@spotshub.com'
select @Name=Name from Store where Name='SpotsHUB'
select @CountryId=Id from Country where Id=1
select @StateProvinceId=Id from StateProvince where Name='British Columbia'
set @City='Vancouver'
set @Address1='Unit 20 - 91 Golden Drive, Coquitlam'
set @ZipPostalCode='V3K 6R2'
set @PhoneNumber='604-290-9888'
if not Exists(select * from [Branch] where Name='SpotsHUB')
insert into [dbo].[Branch](StoreId,Name,Email,CountryId,StateProvinceId,City,Address1,ZipPostalCode,PhoneNumber,CreatedOnUtc,DisplayOrder,StoreFrontPictureId,DistrictId)
			        values(@StoreId,@Name,@Email,@CountryId,@StateProvinceId,@City,@Address1,@ZipPostalCode,@PhoneNumber,getdate(),0,0,1)
end


IF EXISTS (SELECT COLUMNPROPERTY(OBJECT_ID('Order'),'ShipmentDate','AllowsNull') AS 'AllowsNull')
	ALTER TABLE "Order" ALTER COLUMN ShipmentDate date NULL
IF EXISTS (SELECT COLUMNPROPERTY(OBJECT_ID('Order'),'ShippingTimeWindowsId','AllowsNull') AS 'AllowsNull')
	ALTER TABLE "Order" ALTER COLUMN ShippingTimeWindowsId int NULL

IF NOT EXISTS(select * from LocaleStringResource where ResourceName='Admin.Orders.Fields.OrderKindStatus')
 insert into LocaleStringResource(LanguageId,ResourceName,ResourceValue)Values(1,'Admin.Orders.Fields.OrderKindStatus','OrderKindStatus')

 -- Add table to keep mapping between requirement and customers
IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Discount_Plugin_AssignToCustomers')
BEGIN
    CREATE TABLE [dbo].[Discount_Plugin_AssignToCustomers](
	[RequirementId] [int] NOT NULL,
	[CustomerId] [int] NOT NULL,
	[CreatedOnUtc] [datetime] NOT NULL,
		PRIMARY KEY CLUSTERED 
		(
			[RequirementId] ASC,
			[CustomerId] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
	)

	ALTER TABLE [dbo].[Discount_Plugin_AssignToCustomers]  WITH CHECK ADD  CONSTRAINT [FK_AssignToCustomers_CustomerId] FOREIGN KEY([CustomerId])
	REFERENCES [dbo].[Customer] ([Id])

	ALTER TABLE [dbo].[Discount_Plugin_AssignToCustomers] CHECK CONSTRAINT [FK_AssignToCustomers_CustomerId]
	
	ALTER TABLE [dbo].[Discount_Plugin_AssignToCustomers]  WITH CHECK ADD  CONSTRAINT [FK_AssignToCustomers_RequirementId] FOREIGN KEY([RequirementId])
	REFERENCES [dbo].[DiscountRequirement] ([Id])
	
	ALTER TABLE [dbo].[Discount_Plugin_AssignToCustomers] CHECK CONSTRAINT [FK_AssignToCustomers_RequirementId]

END


 IF NOT EXISTS
(
	SELECT top 1 * FROM LocaleStringResource where LanguageId = 1 and ResourceName = 'Logistics.Order.Package.Size.E-commerce'
)
INSERT INTO
LocaleStringResource
(
	LanguageId,ResourceName,ResourceValue
)
VALUES
(
	1,'Logistics.Order.Package.Size.E-commerce','E-commerce Flat Rate'
)

IF EXISTS(select Id from LocaleStringResource where ResourceName='Common.WrongEmail')
update LocaleStringResource set ResourceValue='The format of email is invalid.' where Id=(select Id from LocaleStringResource where ResourceName='Common.WrongEmail')

IF EXISTS(select Id from LocaleStringResource where ResourceName='ShoppingCart.MaximumQuantity')
update LocaleStringResource set ResourceValue='Should not show if I only wanted to purchase {0}.' where Id=(select Id from LocaleStringResource where ResourceName='ShoppingCart.MaximumQuantity')



IF COL_LENGTH('Picture', 'FileName') IS NULL
	ALTER TABLE Picture ADD "FileName" NVARCHAR(300) NULL

IF COL_LENGTH('BlogPost', 'PictureId') IS NULL
	ALTER TABLE BlogPost ADD "PictureId" INT NULL

IF NOT EXISTS (SELECT * FROM dbo.PermissionRecord WHERE SystemName='ManagePictures')
BEGIN
	INSERT INTO dbo.PermissionRecord (Name, SystemName, Category) VALUES ('Admin area. Manage Pictures', 'ManagePictures', 'Configuration')
	INSERT INTO PermissionRecord_Role_Mapping
	(
		CustomerRole_Id,PermissionRecord_Id
	)
	VALUES
	(
		(SELECT ID FROM CustomerRole where Name = 'Administrator'),
		(SELECT ID FROM PermissionRecord where SystemName = 'ManagePictures')
	)
END

IF NOT EXISTS (SELECT * FROM dbo.LocaleStringResource WHERE ResourceName='admin.contentmanagement.pictures.uploaded')
INSERT INTO dbo.LocaleStringResource
        ( LanguageId ,
          ResourceName ,
          ResourceValue
        )
VALUES  ( 1 ,
          N'admin.contentmanagement.pictures.manage' ,
          N'Manage Pictures'
        )

IF NOT EXISTS(select * from LocaleStringResource where ResourceName='Admin.Orders.Fields.Boundinvoice')
INSERT INTO LocaleStringResource(LanguageId,ResourceName,ResourceValue)Values(1,'Admin.Orders.Fields.Boundinvoice','Bound Invoice')